package com.vasthu.core.dao;

import com.vasthu.api.dao.CategoryDAO;
import com.vasthu.api.domain.Category;
import com.vasthu.utility.VstDBManager;
import java.util.*;
import java.sql.*;
import org.apache.log4j.Logger;

public class CategoryDAOImpl implements CategoryDAO {

    static Logger logger = Logger.getLogger(CategoryDAOImpl.class);

    public boolean insertCategory(Category cat) {
        if (cat == null) {
            return false;
        }

        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("INSERT INTO Category(cat_name, cat_desc,measuring_unit, status) VALUES(?, ?, ?, 1)");

            pst.setString(1, cat.getCategoryName());
            pst.setString(2, cat.getCategoryDescription());
            pst.setString(3, cat.getMeasuringUnit());

            pst.executeUpdate();

        } catch (Exception ex) {
            logger.error("Unable to insert given catergory");
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    public Category getCategoryById(long catId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Category cat = new Category();
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Category WHERE cat_id = ? AND status = 1");
            pst.setLong(1, catId);
            rs = pst.executeQuery();

            while (rs.next()) {
                cat.setCategoryId(rs.getInt("cat_id"));
                cat.setCategoryName(rs.getString("cat_name"));
                cat.setCategoryDescription(rs.getString("cat_desc"));
                cat.setMeasuringUnit(rs.getString("measuring_unit"));
                break;
            }

        } catch (Exception ex) {
            System.out.println("ERROR : CategoryDAOImpl->deleteCategoryById()");
        } finally {
            VstDBManager.get().close();
        }
        return cat;
    }

    public Category getCategoryByName(String catName) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Category cat = new Category();
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Category WHERE cat_name = ? AND status = 1");
            pst.setString(1, catName);
            rs = pst.executeQuery();

            while (rs.next()) {
                cat.setCategoryId(rs.getInt("cat_id"));
                cat.setCategoryName(rs.getString("cat_name"));
                cat.setCategoryDescription(rs.getString("cat_desc"));
                cat.setMeasuringUnit(rs.getString("measuring_unit"));
                break;
            }

        } catch (Exception ex) {
            System.out.println("ERROR : CategoryDAOImpl->deleteCategoryByName()");
        } finally {
            VstDBManager.get().close();
        }
        return cat;
    }

    public boolean deleteCategoryById(long catId) {
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("DELETE FROM Category WHERE cat_id = ?");
            pst.setLong(1, catId);
            pst.executeUpdate();
        } catch (Exception ex) {
            System.out.println("ERROR : CategoryDAOImpl->deleteCategoryById()");
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    public List<Category> getAllCategorys() {
        List<Category> catList = new ArrayList<Category>();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Category WHERE status = 1");
            rs = pst.executeQuery();

            while (rs.next()) {
                Category cat = new Category();

                cat.setCategoryId(rs.getInt("cat_id"));
                cat.setCategoryName(rs.getString("cat_name"));
                cat.setCategoryDescription(rs.getString("cat_desc"));
                cat.setMeasuringUnit(rs.getString("measuring_unit"));
                catList.add(cat);
            }

        } catch (SQLException ex) {
            System.out.println("ERROR : UserDAOImpl->getAllUsers()");
        } finally {
            VstDBManager.get().close();
        }
        return catList;
    }
}